package handler;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

import static Util.JdbcUtil.getConnection;

public class CreateTableHandler {

    public static void main(String[] args) {
        // 创建表
        createTables();
    }

    public static void createTables() {
        String inventoryInfoTable = """
                CREATE TABLE IF NOT EXISTS `inventoryinfo` (
                    `PartNumber` varchar(20) NOT NULL,
                    `PartName` varchar(100) NOT NULL,
                    `InventoryQuantity` int NOT NULL,
                    `CriticalValue` int NOT NULL,
                    PRIMARY KEY (`PartNumber`)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
                """;

        String orderInfoTable = """
                CREATE TABLE IF NOT EXISTS `orderinfo` (
                    `PartNumber` varchar(20) NOT NULL,
                    `PartName` varchar(100) NOT NULL,
                    `OrderQuantity` int NOT NULL,
                    `CurrentPrice` decimal(10,2) NOT NULL,
                    `MainSupplier` varchar(100) NOT NULL,
                    `TimesSupplier` varchar(100) DEFAULT NULL,
                    PRIMARY KEY (`PartNumber`),
                    CONSTRAINT `orderinfo_ibfk_1` FOREIGN KEY (`PartNumber`) REFERENCES `inventoryinfo` (`PartNumber`) ON DELETE CASCADE
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
                """;

        String transactionTable = """
                CREATE TABLE IF NOT EXISTS `transaction` (
                    `TransactionNumber` varchar(20) NOT NULL,
                    `PartNumber` varchar(20) NOT NULL,
                    `TransactionType` enum('Inbound','Outbound') NOT NULL,
                    `Quantity` int NOT NULL,
                    `UpdateStatus` tinyint(1) NOT NULL DEFAULT '0',
                    PRIMARY KEY (`TransactionNumber`),
                    KEY `PartNumber` (`PartNumber`),
                    CONSTRAINT `transaction_ibfk_1` FOREIGN KEY (`PartNumber`) REFERENCES `inventoryinfo` (`PartNumber`) ON DELETE CASCADE
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
                """;

        try (Connection connection = getConnection();
             Statement statement = connection.createStatement()) {

            // 创建表
            statement.executeUpdate(inventoryInfoTable);
            System.out.println("Table 'inventoryinfo' created successfully.");

            statement.executeUpdate(orderInfoTable);
            System.out.println("Table 'orderinfo' created successfully.");

            statement.executeUpdate(transactionTable);
            System.out.println("Table 'transaction' created successfully.");

        } catch (SQLException e) {
            System.err.println("Error creating tables: " + e.getMessage());
        }
    }
}
